/*
Run this script on:

        mssql-tl.db.jwan.it,14444.TTTLines2013    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 02/04/2014 12:17:26

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[jb_voucher_dett_arch]'
GO
CREATE TABLE [dbo].[jb_voucher_dett_arch]
(
[IDVOUCHER] [int] NOT NULL,
[N_MOD] [int] NOT NULL,
[CODITINERA] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATAORAPART] [datetime] NOT NULL,
[CODCORSA_INIZIALE] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CATKEY] [int] NOT NULL,
[CODSCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[QTA] [decimal] (18, 2) NOT NULL,
[DATA_INSERIMENTO] [datetime] NOT NULL,
[COD_OP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTE_GUIDA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDPOSTAZIONE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CODTURNO] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_remote_clienti]'
GO
CREATE TABLE [dbo].[jb_remote_clienti]
(
[IDCLIENTE_REMOTE] [int] NOT NULL IDENTITY(1, 1),
[CODCLIENTE_EXT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODCOMP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NOME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COGNOME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INDIRIZZO] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITTA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TELEFONO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CODICEFISCALE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PIVA] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATA_NASCITA] [smalldatetime] NULL,
[LUOGO_NASCITA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NAZIONE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_remote_clienti] on [dbo].[jb_remote_clienti]'
GO
ALTER TABLE [dbo].[jb_remote_clienti] ADD CONSTRAINT [PK_jb_remote_clienti] PRIMARY KEY CLUSTERED  ([IDCLIENTE_REMOTE])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_postazioniStampe]'
GO
CREATE TABLE [dbo].[jb_postazioniStampe]
(
[IDPOSTAZIONE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODICE_STAMPA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IS_DEFAULT] [bit] NOT NULL DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__jb_postazioniStampe] on [dbo].[jb_postazioniStampe]'
GO
ALTER TABLE [dbo].[jb_postazioniStampe] ADD CONSTRAINT [PK__jb_postazioniStampe] PRIMARY KEY NONCLUSTERED  ([IDPOSTAZIONE], [CODICE_STAMPA])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_remote_abbonamenti_itiner]'
GO
CREATE TABLE [dbo].[jb_remote_abbonamenti_itiner]
(
[CODITINER_LOCAL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODITINER_REMOTE] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODCOMP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_remote_jb_itinerabb] on [dbo].[jb_remote_abbonamenti_itiner]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_itiner] ADD CONSTRAINT [PK_remote_jb_itinerabb] PRIMARY KEY CLUSTERED  ([CODITINER_REMOTE], [CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_remote_abbonamenti_porti]'
GO
CREATE TABLE [dbo].[jb_remote_abbonamenti_porti]
(
[CODPORTO_LOCAL] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODPORTO_REMOTE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODCOMP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_remote_jb_portiabb] on [dbo].[jb_remote_abbonamenti_porti]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_porti] ADD CONSTRAINT [PK_remote_jb_portiabb] PRIMARY KEY CLUSTERED  ([CODPORTO_REMOTE], [CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_personalizzazioni]'
GO
CREATE TABLE [dbo].[jb_personalizzazioni]
(
[CHIAVE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[VALORE] [varchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NOTE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INAMOVIBILE] [bit] NOT NULL DEFAULT ((0)),
[MODIFICABILE] [bit] NOT NULL DEFAULT ((0)),
[ORDINE] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_personalizzazioni] on [dbo].[jb_personalizzazioni]'
GO
ALTER TABLE [dbo].[jb_personalizzazioni] ADD CONSTRAINT [PK_jb_personalizzazioni] PRIMARY KEY CLUSTERED  ([CHIAVE])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_voucher_dett]'
GO
CREATE TABLE [dbo].[jb_voucher_dett]
(
[IDVOUCHER] [int] NOT NULL,
[N_MOD] [int] NOT NULL,
[CODITINERA] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATAORAPART] [datetime] NOT NULL,
[CODCORSA_INIZIALE] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CATKEY] [int] NOT NULL,
[CODSCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[QTA] [decimal] (18, 2) NOT NULL,
[DATA_INSERIMENTO] [datetime] NOT NULL DEFAULT (getdate()),
[COD_OP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTE_GUIDA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDPOSTAZIONE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CODTURNO] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK__jb_voucher_dett] on [dbo].[jb_voucher_dett]'
GO
ALTER TABLE [dbo].[jb_voucher_dett] ADD CONSTRAINT [PK__jb_voucher_dett] PRIMARY KEY NONCLUSTERED  ([IDVOUCHER], [N_MOD], [CODCORSA_INIZIALE], [CATKEY], [CODSCO])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[jb_remote_abbonamenti_passaggi]'
GO
CREATE TABLE [dbo].[jb_remote_abbonamenti_passaggi]
(
[ID] [int] NOT NULL,
[ID_TESSERA] [int] NOT NULL,
[CODCOMP] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TIPOMOV] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CODITINERA] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DATAMOV] [datetime] NOT NULL,
[DATAUPD] [datetime] NOT NULL DEFAULT (getdate()),
[DA_RIMUOVERE] [bit] NOT NULL DEFAULT ((0))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_jb_remote_abbonamenti_passaggi] on [dbo].[jb_remote_abbonamenti_passaggi]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_passaggi] ADD CONSTRAINT [PK_jb_remote_abbonamenti_passaggi] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[jb_remote_clienti]'
GO
ALTER TABLE [dbo].[jb_remote_clienti] ADD CONSTRAINT [UNIQUE_CLIENTE_REMOTE] UNIQUE NONCLUSTERED  ([CODCLIENTE_EXT], [CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_postazioniStampe]'
GO
ALTER TABLE [dbo].[jb_postazioniStampe] ADD
CONSTRAINT [FK_jb_postazioniStampe_jb_postazioni] FOREIGN KEY ([IDPOSTAZIONE]) REFERENCES [dbo].[jb_postazioni] ([IDPOSTAZIONE])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_remote_abbonamenti_itiner]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_itiner] ADD
CONSTRAINT [FK_jb_remote_abbonamenti_itiner_itiner] FOREIGN KEY ([CODITINER_LOCAL]) REFERENCES [dbo].[itiner] ([CODITINERA]),
CONSTRAINT [FK_jb_remote_abbonamenti_itiner_compagnie] FOREIGN KEY ([CODCOMP]) REFERENCES [dbo].[compagnie] ([CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_remote_abbonamenti_passaggi]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_passaggi] ADD
CONSTRAINT [FK_jb_remote_abbonamenti_passaggi_jb_tessere] FOREIGN KEY ([ID_TESSERA]) REFERENCES [dbo].[jb_tessere] ([ID_TESSERA]),
CONSTRAINT [FK_jb_remote_abbonamenti_passaggi_compagnie] FOREIGN KEY ([CODCOMP]) REFERENCES [dbo].[compagnie] ([CODCOMP]),
CONSTRAINT [FK_jb_remote_abbonamenti_passaggi_causalimovim] FOREIGN KEY ([TIPOMOV]) REFERENCES [dbo].[causalimovim] ([TIPOMOV]),
CONSTRAINT [FK_jb_remote_abbonamenti_passaggi_itiner] FOREIGN KEY ([CODITINERA]) REFERENCES [dbo].[itiner] ([CODITINERA])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_remote_abbonamenti_porti]'
GO
ALTER TABLE [dbo].[jb_remote_abbonamenti_porti] ADD
CONSTRAINT [FK_jb_remote_abbonamenti_porti_porti] FOREIGN KEY ([CODPORTO_LOCAL]) REFERENCES [dbo].[porti] ([CODPORTO]),
CONSTRAINT [FK_jb_remote_abbonamenti_porti_compagnie] FOREIGN KEY ([CODCOMP]) REFERENCES [dbo].[compagnie] ([CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_remote_clienti]'
GO
ALTER TABLE [dbo].[jb_remote_clienti] ADD
CONSTRAINT [FK_jb_remote_clienti_compagnie] FOREIGN KEY ([CODCOMP]) REFERENCES [dbo].[compagnie] ([CODCOMP])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[jb_voucher_dett]'
GO
ALTER TABLE [dbo].[jb_voucher_dett] ADD
CONSTRAINT [FK_jb_voucher_dett_itiner] FOREIGN KEY ([CODITINERA]) REFERENCES [dbo].[itiner] ([CODITINERA]),
CONSTRAINT [FK_jb_voucher_dett_corse_iniziale] FOREIGN KEY ([CODCORSA_INIZIALE]) REFERENCES [dbo].[corse] ([CODCORSA]),
CONSTRAINT [FK_jb_voucher_dett_jb_categoSconti] FOREIGN KEY ([CATKEY], [CODSCO]) REFERENCES [dbo].[jb_categoSconti] ([CATKEY], [CODSCO]),
CONSTRAINT [FK_jb_voucher_dett_jb_turni] FOREIGN KEY ([IDPOSTAZIONE], [CODTURNO]) REFERENCES [dbo].[jb_turni] ([IDPOSTAZIONE], [CODTURNO])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[jb_postazioniStampe]'
GO
GRANT SELECT ON  [dbo].[jb_postazioniStampe] TO [JamboClient]
GRANT SELECT ON  [dbo].[jb_postazioniStampe] TO [JamboGest]
GRANT INSERT ON  [dbo].[jb_postazioniStampe] TO [JamboGest]
GRANT DELETE ON  [dbo].[jb_postazioniStampe] TO [JamboGest]
GRANT UPDATE ON  [dbo].[jb_postazioniStampe] TO [JamboGest]
GO
PRINT N'Altering permissions on [dbo].[jb_remote_abbonamenti_itiner]'
GO
GRANT SELECT ON  [dbo].[jb_remote_abbonamenti_itiner] TO [JamboClient]
GRANT INSERT ON  [dbo].[jb_remote_abbonamenti_itiner] TO [JamboClient]
GRANT DELETE ON  [dbo].[jb_remote_abbonamenti_itiner] TO [JamboClient]
GRANT UPDATE ON  [dbo].[jb_remote_abbonamenti_itiner] TO [JamboClient]
GO
PRINT N'Altering permissions on [dbo].[jb_remote_abbonamenti_passaggi]'
GO
GRANT INSERT ON  [dbo].[jb_remote_abbonamenti_passaggi] TO [sync_abb]
GRANT SELECT ON  [dbo].[jb_remote_abbonamenti_passaggi] TO [sync_jb_abbonamenti]
GRANT INSERT ON  [dbo].[jb_remote_abbonamenti_passaggi] TO [sync_jb_abbonamenti]
GO
PRINT N'Altering permissions on [dbo].[jb_remote_abbonamenti_porti]'
GO
GRANT SELECT ON  [dbo].[jb_remote_abbonamenti_porti] TO [JamboClient]
GRANT INSERT ON  [dbo].[jb_remote_abbonamenti_porti] TO [JamboClient]
GRANT DELETE ON  [dbo].[jb_remote_abbonamenti_porti] TO [JamboClient]
GRANT UPDATE ON  [dbo].[jb_remote_abbonamenti_porti] TO [JamboClient]
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
